<?php defined('SYSPATH') or die('No direct script access.');
/**
 * 
 * 采购付款单批量上传
 * @author locove
 *
 */
class Controller_Uploadpays extends Controller_Admin{
    /**
     * (non-PHPdoc)
     * @see Controller_Admin::before()
     */
	public function before()
    {
    	try {
    		  ACL::Required('uploadpays');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
    
    /**
     * 首页
     */
    public function action_index()
    {
		$this->title=('采购付款单上传');
		$view=View::factory('money/uploadpays');
		$this->response->body($view);	
	}
	
	/**
	 * 执行付款单批量上传
	 */
	public function action_upload_pays()
	{
		set_time_limit(0);
		$view=View::factory("money/uploadpays");
		$this->title='采购付款单批量上传结果';
		$userid = User::active_user()->id;
	 	ini_set('memory_limit','500M');
	 	require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
	 	$xls= $_FILES['xls']['tmp_name'];
	 	$objPHPExcel= PHPExcel_IOFactory::load($xls);
	 	$data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
		$num = count($data);
		$error_arr=array();//存储不合格的行
		$counter=0;
		for($i=2;$i<=$num;$i++){
			$purchaseorder_id=intval(trim($data[$i]['A']));
			$purchaseorder = ORM::factory('purchaseorder')
					->where('id', '=', $purchaseorder_id)
					->where('status', '>=', '20')
					->where('status', '<=', '90')->find();
			if($purchaseorder->id!=''){
				if(!(preg_match('/^[-]?\d+(?:\.\d+)?$/',trim($data[$i]['B']))&&trim($data[$i]['B'])!=0)){
					$counter++;
					$error_arr[1][]=$i;
				}
			}else{
				$counter++;
				$error_arr[0][]=$i;
			}
		}
	 	if($counter>0){
	 		$view->message='提交成功0行,有'.$counter.'条记录有误,请更改后重新上传:';
	 		$view->set('error_arr',$error_arr);
	 	}else{
	 		for($i=2;$i<=$num;$i++){
		 		$purchaseorder_id=intval(trim($data[$i]['A']));
				$purchaseorder = ORM::factory('purchaseorder')
						->where('id', '=', $purchaseorder_id)
						->where('status', '>=', '20')
						->where('status', '<=', '90')->find();
		 		$ok=ORM::factory('purchaseorder_pay');
				$ok->purchaseorder_id=$purchaseorder_id;
				$ok->supplier_id=$purchaseorder->supplier_id;
				$ok->pay_way=$purchaseorder->pay_way;
				$ok->pay_date=date('Y-m-d H:i:s');
				$ok->payer=$userid;
				$ok->amount_paid=trim($data[$i]['B']);
				$ok->remark=trim($data[$i]['C']);
				$ok->save();
	 		}
	 		$view->message='成功上传'.($num-1).'条记录!';
	 	}
	 	$this->response->body($view);		
	}
	
	/**
	 * 下载采购付款单模板
	 */
	public function action_down_template()
	{
		$this->title='下载模版';
		require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
		$this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
		$objPHPExcel      = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator("newerp")
		->setLastModifiedBy("newerp")
		->setTitle("Office 2003 XLSX Test Document")
		->setSubject("Office 2003 XLSX Test Document")
		->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
		->setKeywords("office 2003 openxml php")
		->setCategory("Test result file");
		$objActSheet =$objPHPExcel->setActiveSheetIndex(0);
		$objActSheet->setCellValue('A1', '采购单号');
		$objActSheet->setCellValue('B1', '付款金额');
		$objActSheet->setCellValue('C1', '备注');
		$objPHPExcel->getActiveSheet()->setTitle('uploadpays');
		$objPHPExcel->setActiveSheetIndex(0);
		$day=date("m-d");
		header("Content-Type: application/vnd.ms-excel; charset=utf-8");
		header('Content-Disposition: attachment;filename="'.$day.'采购付款单.xls"');
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;		
	}
	
	/**
	 * 单条录入悬浮框
	 */
	/*public function action_*/
	public function action_db()
    {
		
    $connection=mysql_connect("192.168.5.88",
    "root","ko813root") or die("连接服务器失败");  
    mysql_select_db("itnewerp",$connection)
     or die("选择数据库失败");  
    $query="select * from waster_receipts";  
    $result=mysql_query($query) or 
    die("查询用户失败");  
    echo mysql_field_name($result,0);
     //输出第一个字段名  
    echo "<br>";  
    echo mysql_field_name($result,1); 
    //输出第二个字段名  
    mysql_close($connection);   
    	
    }
}
